"""
Case Type   : 慢SQL记录系统表
Case Name   : 主机测试设置关闭全量SQL记录开关，打开慢SQL记录L0级别开关，配置log_min_duration_statement=1000,
              通过jdbc执行查询语句，测试是否记录大于该阈值的慢SQL
Create At   : 2024-10-10
Owner       : lvlintao
Description :
    1、查询enable_stmt_track是否为on，关闭全量SQL记录开关，打开慢SQL记录L0级别开关，
       配置log_min_duration_statement=1000
    2、建表插入数据
    3、scp相关java文件
    4、编译java文件
    5、运行java预编译代码
    6、查询statement_history表记录
    7、清理环境，恢复默认值
Expect      :
    1、显示默认值，on;
    2、建表插入数据成功;
    3、scp成功
    4、编译成功
    5、运行成功
    6、查询成功
    7、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.pri_root = Node('PrimaryRoot')
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, 'JDBC_test')
        self.new_jdbc_path = os.path.join(self.target_path, 'postgresql.jar')
        self.db_host = self.pri_root.db_host
        self.db_port = self.pri_root.db_port
        self.db_name = self.pri_root.db_name
        self.username = self.pri_root.db_user
        self.passwd = self.pri_root.db_password
        self.java_name = "Opengauss_Function_SlowSQL_Record_Case0031"
        self.show_enable_stmt = 'show enable_stmt_track;'
        self.set_track_level = "track_stmt_stat_level='OFF,L0'"
        self.set_duration_statement = 'log_min_duration_statement=1000ms'
        self.reset_duration_statement = 'log_min_duration_statement=30min'
        self.set_instr_count = "instr_unique_sql_count=20000"
        self.reset_instr_count = "instr_unique_sql_count=100"
        self.show_instr_count = "show instr_unique_sql_count;"
        self.prepare_data = f"drop table if exists query_plan_table;" \
                            f"create table query_plan_table(id int,num int);" \
                            f"create unique index idIndex on query_plan_table(id);" \
                            f"insert into query_plan_table " \
                            f"select generate_series(1,10000),generate_series(1,10000);"
        self.query_stat = f"select pg_sleep(5);" \
                          f"select count(*) from statement_history where " \
                          f"query like '%from query_plan_table%' and query_plan is not null;"
        self.clean_history = f"delete statement_history where query like '%from query_plan_table%';"
        self.clean_sql = f"delete statement_history where query like '%from query_plan_table%';" \
                         f"drop index idindex;" \
                         f"drop table query_plan_table;"
        self.show_track_level = 'show track_stmt_stat_level;'
        self.show_duration_statement = 'show log_min_duration_statement;'

    def test_slowsql(self):
        text = '--step1:查询enable_stmt_track;expect:默认为on--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_enable_stmt)
        logger.info(show_cmd)
        self.assertEquals('on', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_track_level)
        logger.info(set_cmd1)
        show_res = self.comsh.execut_db_sql(self.show_track_level)
        logger.info(show_res)
        self.assertEquals('OFF,L0', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)

        set_cmd2 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_duration_statement)
        logger.info(set_cmd2)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('1s', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd3 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_instr_count)
        logger.info(set_cmd2)
        show_res = self.comsh.execut_db_sql(self.show_instr_count)
        logger.info(show_res)
        self.assertEquals('20000', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)

        text = '--step2:创建表,插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data, dbname=self.db_name)
        logger.info(data_cmd)
        self.assertIn('INSERT 0 10000', data_cmd,
                      '执行失败' + text)

        step_txt = '----step3: scp相关java; expect:创建成功----'
        logger.info(step_txt)
        self.com.scp_file(self.pri_root, f'{self.java_name}.java',
                          self.target_path)
        check_cmd = f'ls {self.target_path}'
        check_result = self.pri_root.sh(check_cmd).result()
        self.assertIn(self.java_name, check_result, '执行失败' + step_txt)

        step_txt = '----step4: 编译java文件; expect:编译成功----'
        logger.info(step_txt)
        self.pri_root.scp_put(macro.JDBC_PATH, os.path.join(self.target_path,
                                                            'postgresql.jar'))
        javac_cmd = f"javac -encoding utf-8 -cp " \
                    f"{os.path.join(self.target_path, 'postgresql.jar')} " \
                    f"{os.path.join(self.target_path, f'{self.java_name}.java')}"
        logger.info(javac_cmd)
        javac_result = self.pri_root.sh(javac_cmd).result()
        logger.info(javac_result)
        self.assertEqual('', javac_result, "执行失败" + step_txt)
        time.sleep(3)

        step_txt = '----step5:运行java预编译代码;expect:运行成功-----'
        logger.info(step_txt)
        clean_res = self.comsh.execut_db_sql(self.clean_history, dbname='postgres')
        logger.info(clean_res)
        execute_cmd = f"cd {self.target_path};source /etc/profile;" \
                      f"java -cp {self.new_jdbc_path}:{self.target_path} " \
                      f"{self.java_name} {self.db_host}:{self.db_port} " \
                      f"{self.db_name} {self.username} {self.passwd}"
        logger.info(execute_cmd)
        java_msg = self.pri_root.sh(execute_cmd).result()
        logger.info(java_msg)

        step_txt = '--step6:查询statement_history表记录;expect:查询成功--'
        logger.info(step_txt)
        sql_re = self.comsh.execut_db_sql(self.query_stat, dbname='postgres')
        logger.info(sql_re)
        self.assertEquals('1', sql_re.splitlines()[-2].strip(), '执行失败' + step_txt)

    def tearDown(self):
        text = '--step7:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        logger.info(self.clean_sql)
        clean_res = self.comsh.execut_db_sql(self.clean_sql, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, clean_res,
                      '执行失败' + text)

        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.set_track_level)
        show_res = self.comsh.execut_db_sql(self.show_track_level)
        logger.info(show_res)
        self.assertEquals('OFF,L0', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.reset_duration_statement)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('30min', show_res.splitlines()[-2].strip(),
                         '执行失败' + text)
        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.reset_instr_count)
        show_res = self.comsh.execut_db_sql(self.show_instr_count)
        logger.info(show_res)
        self.assertEquals('100', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
